package ro.qi.framework.sys.db.util;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.HashMap;
import java.util.Vector;

import oracle.jdbc.OracleTypes;
import ro.qi.framework.sys.db.pool.JDCConnection;
import ro.qi.framework.sys.db.pool.JDCConnectionPool;

/**
 * Utility class to manage connections to the underlying Oracle
 * database. Servlets/JSPs which need connection to the DB need to
 * create an instance of this class.
 *
 * @author Srinivas
 */
public class DBConnectionUtil {
	//private static DataSource ds = null;
	private JDCConnection connection = null;
	private String sqlQuery = null;
	private Vector values = null;

	public static String STRING_TYPE = "java.lang.String";
	public static String CHAR_TYPE = "java.lang.Character";
	public static String INT_TYPE = "java.lang.Integer";
	public static String LONG_TYPE = "java.lang.Long";
	public static String FLOAT_TYPE = "java.lang.Float";
	public static String DOUBLE_TYPE = "java.lang.Double";
	public static String SHORT_TYPE = "java.lang.Short";
	public static String BOOL_TYPE = "java.lang.Boolean";
	public static String DATE_TYPE = "java.sql.Date";
	public static String TIME_TYPE = "java.sql.Time";
	public static String BYTE_TYPE = "java.lang.Byte";
	public static String TIMESTAMP_TYPE = "java.sql.Timestamp";
	public static String BIGDECIMAL_TYPE = "java.math.BigDecimal";
	public static String ORACLE_CURSOR_TYPE = "com.qi.fortress.db.util.OracleType";

	public static String INSERT_MODE = "INSERT";
	public static String UPDATE_MODE = "UPDATE";

	private JDCConnectionPool _dbPool;

	/**
	 * Constructor
	 */
	public DBConnectionUtil(JDCConnectionPool dbPool) throws SQLException {
		_dbPool = dbPool;
		connection = getPooledConnection();
		//connection.setAutoCommit(false);
	}

	private JDCConnection getPooledConnection() throws SQLException {
		return _dbPool.getConnection();
	}

	/**
	 * Sets the SQL Query to be executed
	 */
	public void setSQLQuery(String pQuery) {
		sqlQuery = pQuery;
	}

	/**
	 * Set the values to be used when executing the query, if
	 * the query uses Prepared Statement format.
	 *
	 * @param Vector Vector containing values for place-holder
	 * 				 arguments in the Prepared statement. Each
	 * 				 Element in the Vector should be a valid
	 * 				 Java Class Type
	 *
	 * 				 If executing a stored procedure, the type of
	 * 				 output parameters should be specified using
	 * 				 the constants defined above
	 */
	public void setValues(Vector pValues) {
		values = pValues;
	}

	/**
	 * Executes the specified SQL string as a query and returns
	 * a Vector with Row objects, or an empty Vector if no rows
	 * where found.
	 *
	 * @return a Vector of Row objects
	 * @exception SQLException
	 * @exception UnsupportedTypeException, if the returned value
	 *            doesn't match any Value subclass
	 */
	public Vector executeQuery() throws SQLException, UnsupportedTypeException {
		Vector rows = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;

		try {
			if (values != null && values.size() > 0) {
				// Use a PreparedStatement and set all values
				pstmt = connection.prepareStatement(sqlQuery);
				setValues(pstmt, values);
				rs = pstmt.executeQuery();
			} else {
				// Use a regular Statement
				stmt = connection.createStatement();
				rs = stmt.executeQuery(sqlQuery);
			}
			// Save the result in a Vector of Row object
			rows = toVector(rs);
			//connection.commit();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				throw e;
				// Ignore. Probably caused by a previous SQLException thrown
				// by the outer try block
			}
		}
		return rows;
	}

	/**
	 * Executes the specified SQL string (any statement except SELECT, such
	 * as UPDATE, INSERT, DELETE or CREATE TABLE) and returns
	 * the number of rows affected by the statement, or 0 if none.
	 *
	 * @return the number of rows affected
	 * @exception SQLException
	 */
	public int executeUpdate() throws SQLException {
		int noOfRows = 0;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;

		try {
			if (values != null && values.size() > 0) {
				// Use a PreparedStatement and set all values
				pstmt = connection.prepareStatement(sqlQuery);
				setValues(pstmt, values);
				noOfRows = pstmt.executeUpdate();
			} else {
				// Use a regular Statement
				stmt = connection.createStatement();
				noOfRows = stmt.executeUpdate(sqlQuery);
			}
			//connection.commit();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				// Ignore. Probably caused by a previous SQLException thrown
				// by the outer try block
			}
		}
		return noOfRows;
	}

	/**
	 * Executes the Stored procedure specified in the SQL
	 * Query String. This Query String should be of the form:
	 *
	 *       "{Call procedureName(?, ?)}"
	 *
	 * @return Row -- Row containing the output params' values
	 * @exception SQLException
	 */
	public Row executeStoredProcedure() throws SQLException, Exception {
		CallableStatement _stmt = null;
		HashMap _outputParamMapping = null;
		Row _result = null;

		try {
			if (values != null && values.size() > 0) {
				_stmt = connection.prepareCall(sqlQuery);
				_outputParamMapping = new HashMap();
				setValues(_stmt, values, _outputParamMapping);
				_stmt.execute();
			} else {
				// throw new SQLException("No Parameters specified for the Stored Procedure!");
				_stmt = connection.prepareCall(sqlQuery);
				_outputParamMapping = new HashMap();
				//setValues(_stmt, values, _outputParamMapping);
				_stmt.execute();
			}
			_result = new Row(_stmt, _outputParamMapping);
			//connection.commit();
		} finally {
			try {
				if (_stmt != null) {
					_stmt.close();
				}
			} catch (SQLException e) {
				// Ignore. Probably caused by a previous SQLException thrown
				// by the outer try block
			}
		}

		return _result;

	}

	/**
	 * Calls setXXX() methods on the PreparedStatement for all Value
	 * objects in the values Vector.
	 *
	 * @param pstmt the PreparedStatement
	 * @param values a Vector with Value objects
	 * @exception SQLException
	 */
	private void setValues(PreparedStatement pstmt, Vector values) throws SQLException {
		Object _value = null;

		for (int i = 0; i < values.size(); i++) {
			_value = values.elementAt(i);

			// Note! Set methods are indexed from 1, so we add 1 to i
			if (_value instanceof BigDecimal) {
				pstmt.setBigDecimal(i + 1, (BigDecimal) _value);
			} else if (_value instanceof Boolean) {
				pstmt.setBoolean(i + 1, ((Boolean) _value).booleanValue());
			} else if (_value instanceof Byte) {
				pstmt.setByte(i + 1, ((Byte) _value).byteValue());
			}
			/*else if (_value instanceof Bytes) {
			 pstmt.setBytes(i + 1, v.getBytes());
			 }*/
			else if (_value instanceof java.sql.Date) {
				pstmt.setDate(i + 1, ((java.sql.Date) _value));
			} else if (_value instanceof Double) {
				pstmt.setDouble(i + 1, ((Double) _value).doubleValue());
			} else if (_value instanceof Float) {
				pstmt.setFloat(i + 1, ((Float) _value).floatValue());
			} else if (_value instanceof Integer) {
				pstmt.setInt(i + 1, ((Integer) _value).intValue());
			} else if (_value instanceof Long) {
				pstmt.setLong(i + 1, ((Long) _value).longValue());
			} else if (_value instanceof Short) {
				pstmt.setShort(i + 1, ((Short) _value).shortValue());
			} else if (_value instanceof String) {
				pstmt.setString(i + 1, ((String) _value));
			} else if (_value instanceof Time) {
				pstmt.setTime(i + 1, ((Time) _value));
			} else if (_value instanceof Timestamp) {
				pstmt.setTimestamp(i + 1, ((Timestamp) _value));
			} else if (_value instanceof NullParam) {
				pstmt.setNull(i + 1, ((NullParam) _value).getType());
			} else {
				pstmt.setObject(i + 1, _value);
			}
		}
	}

	/**
	 * Calls setXXX() methods on the CallableStatement for all Value
	 * objects in the values Vector.
	 *
	 * @param pstmt the CallableStatement
	 * @param values a Vector with Value objects
	 * @exception SQLException
	 */
	private void setValues(CallableStatement pstmt, Vector values, HashMap pOutputParamMapping) throws SQLException {
		Object _value = null;

		// Set the first parameter as an output parameter of type
		// Integer

		for (int i = 0; i < values.size(); i++) {
			_value = values.elementAt(i);

			// Note! Set methods are indexed from 1,
			if (_value instanceof String) {
				setParameter(pstmt, ((String) _value), i + 1, pOutputParamMapping);
			} else if (_value instanceof BigDecimal) {
				pstmt.setBigDecimal(i + 1, (BigDecimal) _value);
			} else if (_value instanceof Boolean) {
				pstmt.setBoolean(i + 1, ((Boolean) _value).booleanValue());
			} else if (_value instanceof Byte) {
				pstmt.setByte(i + 1, ((Byte) _value).byteValue());
			}
			/*else if (_value instanceof Bytes) {
			 pstmt.setBytes(i + 1, v.getBytes());
			 }*/
			else if (_value instanceof java.sql.Date) {
				pstmt.setDate(i + 1, ((java.sql.Date) _value));
			} else if (_value instanceof Double) {
				pstmt.setDouble(i + 1, ((Double) _value).doubleValue());
			} else if (_value instanceof Float) {
				pstmt.setFloat(i + 1, ((Float) _value).floatValue());
			} else if (_value instanceof Integer) {
				pstmt.setInt(i + 1, ((Integer) _value).intValue());
			} else if (_value instanceof Long) {
				pstmt.setLong(i + 1, ((Long) _value).longValue());
			} else if (_value instanceof Short) {
				pstmt.setShort(i + 1, ((Short) _value).shortValue());
			} else if (_value instanceof Time) {
				pstmt.setTime(i + 1, ((Time) _value));
			} else if (_value instanceof Timestamp) {
				pstmt.setTimestamp(i + 1, ((Timestamp) _value));
			} else if (_value instanceof NullParam) {
				pstmt.setNull(i + 1, ((NullParam) _value).getType());
			} else {
				pstmt.setObject(i + 1, _value);
			}
		}

		//pstmt.registerOutParameter(1, Types.INTEGER);
		//pOutputParamMapping.put(new Integer(1), INT_TYPE);
	}

	private void setParameter(CallableStatement pstmt, String pValue, int pIndex, HashMap pOutputParamMapping) throws SQLException {
		if (pValue.equals(BIGDECIMAL_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.NUMERIC);
			pOutputParamMapping.put(new Integer(pIndex), BIGDECIMAL_TYPE);
		}
		/*else if (_className.endsWith("Boolean"))
		{
				pstmt.registerOutParameter(pIndex, Types.
		}*/
		else if (pValue.equals(BYTE_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.TINYINT);
			pOutputParamMapping.put(new Integer(pIndex), BYTE_TYPE);
		}
		/*else if (_className instanceof Bytes) {
		 pstmt.setBytes(i + 1, v.getBytes());
		 }*/
		else if (pValue.equals(DATE_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.DATE);
			pOutputParamMapping.put(new Integer(pIndex), DATE_TYPE);
		} else if (pValue.equals(DOUBLE_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.DOUBLE);
			pOutputParamMapping.put(new Integer(pIndex), DOUBLE_TYPE);
		} else if (pValue.equals(FLOAT_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.FLOAT);
			pOutputParamMapping.put(new Integer(pIndex), FLOAT_TYPE);
		} else if (pValue.equals(INT_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.INTEGER);
			pOutputParamMapping.put(new Integer(pIndex), INT_TYPE);
		} else if (pValue.equals(LONG_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.BIGINT);
			pOutputParamMapping.put(new Integer(pIndex), LONG_TYPE);
		} else if (pValue.equals(SHORT_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.SMALLINT);
			pOutputParamMapping.put(new Integer(pIndex), SHORT_TYPE);
		} else if (pValue.equals(STRING_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.VARCHAR);
			pOutputParamMapping.put(new Integer(pIndex), STRING_TYPE);
		} else if (pValue.equals(TIME_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.TIME);
			pOutputParamMapping.put(new Integer(pIndex), TIME_TYPE);
		} else if (pValue.equals(TIMESTAMP_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.TIMESTAMP);
			pOutputParamMapping.put(new Integer(pIndex), TIMESTAMP_TYPE);
		} else if (pValue.equals(CHAR_TYPE)) {
			pstmt.registerOutParameter(pIndex, Types.CHAR);
			pOutputParamMapping.put(new Integer(pIndex), CHAR_TYPE);
		} else if (pValue.equals(ORACLE_CURSOR_TYPE)) {
			pstmt.registerOutParameter(pIndex, OracleTypes.CURSOR);
			pOutputParamMapping.put(new Integer(pIndex), ORACLE_CURSOR_TYPE);
		} else {
			pstmt.setString(pIndex, pValue);
		}
	}

	/**
	 * Gets all data from the ResultSet and returns it as a Vector,
	 * of Row objects.
	 *
	 * @param rs the ResultSet
	 * @return a Vector of Row objects
	 * @exception SQLException, thrown by the JDBC API calls
	 * @exception UnsupportedTypeException, if the returned value
	 *            doesn't match any Value subclass
	 */
	private Vector toVector(ResultSet rs) throws SQLException, UnsupportedTypeException {
		Vector _rows = new Vector();

		while (rs.next()) {
			Row _row = new Row(rs);
			_rows.addElement(_row);
		}
		return _rows;
	}

	/**
	 * Returns the open connection to the connection pool
	 */
	public void closeConnection() throws SQLException {
		_dbPool.returnConnection(connection);
	}
}
